﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aden.Util.Common;
using Aden.Util.Database;
using Aden.Model.Purchase;
using Aden.Model.MenuOrder;
using Aden.Model.Stock;
using System.Data;
using Aden.DAL.MastData;
using Aden.DAL.AppProcess;
using Aden.DAL.StockData;
using Aden.Model.RightsData;
using System.Linq.Dynamic;
using Aden.Model.MenuData;
using System.Web.Http.ModelBinding;
using NPOI.HSSF.Record;

namespace Aden.DAL.Purchase
{
    public class PurchaseOrderFactory
    {
        protected StockTrans stocktrans = null;
        
        public List<Dictionary<string, object>> GetPurchaseItems(string costCenterCode,string requiredDate,string orderType=null)
        {
            try
            {
                string sql = "select division from [dbo].[DM_D_ERP_COSTCENTER] where costcentercode='{0}'";
                sql = string.Format(sql, costCenterCode);
                string dbName = SqlServerHelper.GetDataScalar(SqlServerHelper.salesorderConn(), sql).ToStringTrim();
                if (string.IsNullOrWhiteSpace(dbName))
                    throw new Exception("No db name");

                //PO单用，菜单查询用默认的一个供应商
                List<Dictionary<string, object>> list = GetPurchaseItems(dbName, costCenterCode, requiredDate,orderType);
                
                list = list.GroupBy(q => q["itemCode"]).Select(q =>
                {
                    Dictionary<string, object> dic = new Dictionary<string, object>();
                    dic.Add("itemCode", q.Key);
                    dic.Add("itemName_ZH", q.FirstOrDefault()["itemName_ZH"]);
                    //2020-07-23:Angel Jiang，后续要修改
                    dic.Add("addToMenu", false);
                    dic.Add("suppliers", q.OrderBy(p => p["sortName"]).Select(p =>
                    {
                        Dictionary<string, object> supplier = new Dictionary<string, object>();
                        supplier.Add("supplierCode", p["supplierCode"]);
                        supplier.Add("supplierName", p["supplierName"]);
                        supplier.Add("purchasePrice", p["purchasePrice"]);
                        supplier.Add("purchaseUnit", p["purchaseUnit"]);
                        supplier.Add("conversionRate", p["conversionRate"]);
                        supplier.Add("purStk_Conversion", p["purStk_Conversion"]);
                        supplier.Add("stkBOM_Conversion", p["stkBOM_Conversion"]);
                        supplier.Add("bomUnit", p["bomUnit"]);
                        supplier.Add("stockUnit", p["stockUnit"]);
                        supplier.Add("tax", p["tax"]);

                        return supplier;
                    }).ToList());
                    
                    return dic;
                }).OrderBy(q=>q["itemCode"]).ToList();
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private string SourcePurchaseItemsSql(string db, string date, string fields)
        {
            string sql = " select {2} from dm_d_erp_purchaseagreement_new (nolock) " +
                    " where company='{0}' and condition_code = 'A' and isnull(crdnr,'')<>'' and isnull(validfrom,'2000-1-1')<='{1}' " +
                    " and isnull(validto,'2222-12-31')>='{1}' ";
            sql = string.Format(sql, db, date, fields);
            return sql;
        }

        private List<Dictionary<string, object>> FilterItems(List<Dictionary<string, object>> items, List<Dictionary<string, object>> suppliers)
        {
            if (suppliers == null || !suppliers.Any())
                return items;

            //如果suppliers有值，则items中的记录必须满足suppliers

            //全部供应商和大类
            bool isAll = true;
            // type: Food, NoFood, all。不同type之间的关系是and
            // ccsort: 本CostcenterCode，，公司
            var typesupplier = suppliers.GroupBy(q => q["type"]).Select(q => q.Key);
            foreach (string type in typesupplier)
            {
                var tmp = suppliers.Where(y =>y["type"].ToStr()==type.ToStr() 
                    && y["classCode"].ToStringTrim() == "" && y["supplierCode"].ToStringTrim() == "");
                if (!tmp.Any())// || suppliers.Min(q => q["ccsort"].ToInt()) != tmp.Min(q => q["ccsort"].ToInt()))
                {
                    isAll = false;
                    break;
                }
            }
            if (isAll) return items;

            items = items//.Where(q => q["itemCode"].ToStr() == "1040100124")
                .Select(x =>
            {
                foreach(string type in typesupplier)
                {
                    var tmpSupplierSorts = suppliers.Where(y => y["type"].ToStr() == type.ToStr());
                    // 该产品是Site or Company控制
                    //int minCCSort = tmpSupplierSorts.Min(z => z["ccsort"].ToInt());

                    tmpSupplierSorts = tmpSupplierSorts.Where(y => //y["ccsort"].ToInt() == minCCSort &&
                        (y["classIndex"].ToStr() == "" || y["classCode"].ToStr() == "" ||        //全部分类
                        y["classCode"].ToStr() == x[string.Format("class{0}Code", y["classIndex"].ToStr())].ToStr()) //具体分类
                        && (y["supplierCode"].ToStr() == "" ||                                   //全部供应商
                         y["supplierCode"].ToStr() == x["supplierCode"].ToStr()));
                        
                    if (!tmpSupplierSorts.Any()) return new Dictionary<string, object>();

                    string minSort = tmpSupplierSorts.Min(z => z["sortName"].ToStr());
                    x["sortName"] = x["sortName"].ToStr().CompareTo(minSort)<1?
                        x["sortName"].ToStr() : minSort;
                }

                return x;
            }).Where(q=>q.Any()).ToList();
            return items;
        }

        private List<Dictionary<string,object>> GetPurchaseItems(string db, string costCenterCode, string date, string orderType)
        {
            try
            {
                //允许的产品大类对应的供应商
                List<Dictionary<string, object>> suppliers = (new CClassSupplierFactory()).GetSuppliers(costCenterCode, date, orderType);
                   // (string.IsNullOrWhiteSpace(orderType)? null: string.Format("{0},",orderType)));

                //class的字段
                string tmpClassCode = string.Join(",", suppliers.Where(q => q["classIndex"].ToStringTrim() != "")
                    .Select(q => string.Format("class{0}Code", q["classIndex"])).Distinct()).ToStringTrim();
                
                if (tmpClassCode != "") tmpClassCode = "," + tmpClassCode;                

                #region
                string sql = " select ltrim(rtrim(itemcode)) itemCode,itemName_ZH,ltrim(rtrim(Pur_Unit)) purchaseUnit," +
                    " price purchasePrice, cast(isnull(PurStk_Conversion,1)*isnull(StkBom_Conversion,1) as decimal(18,3)) conversionRate, " +
                    " cast(isnull(purStk_Conversion,1) as decimal(18,3)) purStk_Conversion, " +
                    " cast(isnull(stkBOM_Conversion,1) as decimal(18,3)) stkBOM_Conversion, " +
                    " ltrim(rtrim(BOM_Unit)) bomUnit, ltrim(rtrim(Stock_Unit)) stockUnit, " +
                    " cast(isnull(tax,0) as decimal(18,3)) tax, Supplier_Code supplierCode,supplier_name supplierName,'' sortName{2} " +
                    " from dm_d_erp_purchaseagreement_new (nolock) " +
                    " where division='{0}' and isnull(Supplier_Code,'')<>'' and isnull(validfrom,'2000-1-1')<='{1}' " +
                    " and isnull(validto,'2222-12-31')>='{1}' ; " ;

                #endregion

                sql = string.Format(sql, db, date, tmpClassCode);

                List<Dictionary<string,object>> items1 = SqlServerHelper.GetDictionaryList(SqlServerHelper.salesorderConn(), sql);
                return FilterItems(items1, suppliers);
                #region
                /*
                if (suppliers == null || !suppliers.Any()) return items1;

                IEnumerable<Dictionary<string, object>> tmpSuppliers = suppliers.Where(y => y["classIndex"].ToStringTrim() != "");
                if (tmpSuppliers.Any())
                    items1 = items1.Select(x =>
                    {
                        tmpSuppliers.GroupBy(y => y["classIndex"].ToStringTrim())
                            .OrderByDescending(y => y.Key).ForEach(y =>
                            {
                                var tmpSorts = y.Where(z => (z["classCode"].ToStringTrim() == "" ||     //全部分类
                                    z["classCode"].ToStringTrim() == x[string.Format("class{0}Code", y.Key)].ToStringTrim())
                                    && (z["supplierCode"].ToStringTrim() == "" ||                       //全部供应商
                                    z["supplierCode"].ToStringTrim() == x["supplierCode"].ToStringTrim()));
                                if (tmpSorts.Any())
                                {
                                    x.Add("sortName", tmpSorts.Min(z => z["sortName"].ToStringTrim()));
                                    return;
                                }
                            });
                        return x;
                    }).ToList();

                tmpSuppliers = suppliers.Where(y => y["classIndex"].ToStringTrim() == "");
                if (!items1.Any(q => !q.ContainsKey("sortName")) || !tmpSuppliers.Any())
                    return items1.Where(q => q.ContainsKey("sortName")).ToList();
                

                return items1.Where(q => q.ContainsKey("sortName")).Union(
                    items1.Where(q => !q.ContainsKey("sortName")).Select(q =>
                    {
                        q["sortName"] = tmpSuppliers.Where(p => p["supplierCode"].ToStringTrim() == "" ||
                                        p["supplierCode"].ToStringTrim() == q["supplierCode"].ToStringTrim())
                                        .Min(p => p["sortName"].ToStringTrim());
                        return q;
                    })).ToList();

                
                List<Dictionary<string, object>> items2 = new List<Dictionary<string, object>>();

                Func<Dictionary<string,object>,IEnumerable<Dictionary<string, object>>, Dictionary<string, object>> 
                    func1 = (d, l) =>
                     {
                         d["sortName"] = l.Where(y => y["supplierCode"].ToStringTrim().ToLower() == "" || //全部供应商
                                     y["supplierCode"].ToStringTrim().ToLower() == d["supplierCode"].ToStringTrim().ToLower())
                                    .Min(y => y["sortName"]);
                         
                         return d;
                     };

                Func<Dictionary<string, object>, Dictionary<string, object>,bool> func2 = (a, b) =>
                    {
                        return b["supplierCode"].ToStringTrim().ToLower() == "" ||   //全部供应商
                            b["supplierCode"].ToStringTrim().ToLower() == a["supplierCode"].ToStringTrim().ToLower();
                    };

                //按相同ClassIndex(有值),匹配suppliers及Sort
                suppliers.Where(q=>q["classIndex"].ToStringTrim() != "").OrderByDescending(q=>q["classIndex"])
                    .GroupBy(q => q["classIndex"]).ForEach(q=> {
                        tmpClassCode = string.Format("class{0}Code", q.Key);

                        //相同ClassCode, 得到SupplierCode 
                        items2.AddRange(items1.GroupJoin(q,
                            a => a[tmpClassCode].ToStringTrim().ToLower(), b => b["classCode"].ToStringTrim().ToLower(),
                            (a, b) => new { a, b })
                            .Where(x => x.b.Any(y => func2(x.a, y)))
                            .Select(p => func1(p.a, p.b)));

                        //删除已匹配的item-supplier                
                        items1.RemoveAll(a=>q.Any(b => a[tmpClassCode].ToStringTrim().ToLower() ==
                            b["classCode"].ToStringTrim().ToLower()));
                    });
                
                //items可以全部匹配指定供应商
                if (!items1.Any()) return items2;

                suppliers = suppliers.Where(y => y["classIndex"].ToStringTrim() == "").ToList();

                if (!suppliers.Any()) return items2;

                //所有分类指定的供应商
                items2.AddRange(items1.Where(q => suppliers.Any(y=>func2(q,y)))
                       .Select(q=> func1(q,suppliers)));                       

                return items2;
                */
                #endregion
            }
            catch (Exception e)
            {
                throw e;
            }

        }

        #region 转换成需要保存的采购单记录
        /// <summary>
        /// 根据成本中心、日期导入采购单临时表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int ImportToPurchaseOrder(MenuOrderHead param, string appType)
        {
            PurchaseOrderHead po = new PurchaseOrderHead();

            // 根据日期取得所有的采购单行项
            List<PurchaseOrderLine> lstAllLines = GetAllPurchaseOrderLine(param, appType);
            PurchaseOrderHead poParam = new PurchaseOrderHead();

            // 取得DBCODE
            string strSqlDBCode = " SELECT TOP 1 " +
                                         " DBNAME " +
                                       " , WAREHOUSECODE " +
                                    " FROM CCMAST (NOLOCK)" +
                                   " WHERE COSTCENTERCODE = '{0}' ";
            CCWhs ccwhs = SqlServerHelper.GetEntity<CCWhs>(SqlServerHelper.salesorderConn(), string.Format(strSqlDBCode, param.costCenterCode));

            // 根据条件取得当天已存在的采购单行项（待比对）
            poParam.orderDate = param.requiredDate;
            poParam.costCenterCode = param.costCenterCode;
            poParam.warehouseCode = ccwhs.warehouseCode;
            poParam.dbCode = ccwhs.dbName;
            poParam.lstRequiredDate = param.lstRequiredDate;

            List<PurchaseOrderLine> lstCurLines = new List<PurchaseOrderLine>();

            // 取得既有的采购订单数据
            lstCurLines = GetPurchaseOrderLine(poParam);

            // 取得所有修改和新增的记录
            if (lstCurLines != null && lstCurLines.Count > 0)
            {
                // Menu连接PO取得需要新增和修改的部分
                po.lstPurchaseOrderLine = lstAllLines.GroupJoin(lstCurLines,
                    all => new { all.supplierCode, all.orderDate }, //供应商和订单日期匹配
                    cur => new { cur.supplierCode, cur.orderDate },
                    (all, cur) => new { all, cur })
                    .Where(q => q.cur == null || !q.cur.Any() ||
                        !q.cur.Where(p => p.itemCode == q.all.itemCode &&
                        (p.remark == null ? string.Empty : p.remark) == (q.all.remark == null ? string.Empty : q.all.remark) &&
                        p.qty == q.all.qty).Any())
                    .Select(q =>
                    {
                        if (q.cur == null || !q.cur.Any()) return q.all;
                        // headGuid
                        q.all.headGuid = q.cur.FirstOrDefault().headGuid;

                        var tmp = q.cur.Where(p => p.itemCode == q.all.itemCode);
                        if (tmp.Any()) //供应商，订单日，item 匹配
                            // lineGuid
                            q.all.lineGuid = tmp.FirstOrDefault().lineGuid;

                        return q.all;
                    }).ToList();

                // PO连接Menu取得需要删除的部分
                po.lstPurchaseOrderLine = po.lstPurchaseOrderLine.Concat(lstCurLines.GroupJoin(lstAllLines,
                    cur => new { cur.supplierCode, cur.orderDate },
                    all => new { all.supplierCode, all.orderDate },
                    (cur, all) => new { cur, all })
                .Where(q => q.all == null || !q.all.Any() ||
                    !q.all.Where(p => p.itemCode == q.cur.itemCode).Any())
                    .Select(q =>
                    {
                        // Head DelFlag
                        if (q.all == null || !q.all.Any())
                        {
                            q.cur.delFlag = true;
                            return q.cur;
                        }
                        // Line DelFlag
                        var tmp = q.all.Where(r => r.itemCode == q.cur.itemCode);
                        if (!tmp.Any())
                            q.cur.delFlag = true;
                        return q.cur;
                    }).Where(r => r.delFlag)).ToList();
            }
            else
            {
                po.lstPurchaseOrderLine = lstAllLines;
            }
            po.dbCode = poParam.dbCode;
            po.costCenterCode = poParam.costCenterCode;
            po.warehouseCode = poParam.warehouseCode;
            po.userId = param.createUser;
            po.employee = "1";  // (new SOAccount.AccountFactory()).getEmployeeId(param.createUser);

            return SavePurchaseOrder(po, param, appType);
        }
        #endregion

        public List<OrderType> GetTransType (string orderName)
        {
            string strTransType = "SELECT GUID, TRANSTYPE, BUSINESSTYPE FROM TBLTRANSTYPE WHERE ORDERNAME='{0}'";
            strTransType = string.Format(strTransType, orderName);
            List<OrderType> TransTypeList = SqlServerHelper.GetEntityList<OrderType>(SqlServerHelper.salesorderConn(), strTransType);
            return TransTypeList;
        }

        #region SaveOrderLines
        /// <summary>
        /// Menu To OrderHead / OrderLines
        /// </summary>
        /// <param name="po"></param>
        /// <param name="fromType"></param>
        /// <returns></returns>
        public StringBuilder SavePO(List<MenuOrderHead> lstMenuOrderHead,  string userId, string appType, string status, string appGuid, string fromType, 
            string stoGuid)
        {
            StringBuilder sb = new StringBuilder();

            List<DateTime> requiredDates = lstMenuOrderHead.Select(dr => DateTime.Parse(dr.requiredDate)).Distinct().ToList();
            if (requiredDates == null || !requiredDates.Any())
                return sb;

            //  reauiredDate and headGuid
            var requiredDateList = requiredDates.Select(q => new {
                orderDate = q,
                headGuid = Guid.NewGuid().ToStringTrim()
            }).ToList();

            string costCenterCode = lstMenuOrderHead[0].costCenterCode;
            // 获取TransType
            List<OrderType> TransTypeList = GetTransType("StockIn");
            string transTypeGUID = TransTypeList == null || !TransTypeList.Any() ? string.Empty : TransTypeList.FirstOrDefault().guid;
            string transType = TransTypeList == null || !TransTypeList.Any() ? string.Empty : TransTypeList.FirstOrDefault().transType;
            string businessType = TransTypeList == null || !TransTypeList.Any() ? string.Empty : TransTypeList.FirstOrDefault().businessType;
            string createUser = userId;
            string now = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");

            // TBLORDERLINES 
            string sql = "SELECT DISTINCT A.HEADGUID, A.ORDERDATE FROM TBLORDERHEAD (NOLOCK) A, TBLORDERLINES (NOLOCK) B " +
               "WHERE A.HEADGUID=B.HEADGUID AND A.COSTCENTERCODE='{0}' AND B.APPTYPE='{1}' AND A.ORDERDATE IN ('{2}') " ;
            sql = string.Format(sql, costCenterCode, appType, string.Join("','", requiredDateList.Select(dr => dr.orderDate)));
            List<OrderHead> changedDates = SqlServerHelper.GetEntityList<OrderHead>(SqlServerHelper.salesorderConn(), sql);

            if (changedDates != null)
                requiredDateList = requiredDateList.GroupJoin(changedDates, a => a.orderDate, b => b.orderDate, (a, b) => new
                {
                    orderDate = a.orderDate,
                    headGuid = b.Any() ? b.FirstOrDefault().headGuid : a.headGuid
                }).ToList();

            // New OrderLines
            List<MenuOrderLine> data = lstMenuOrderHead.Where(dr => dr.costCenterCode == costCenterCode // && string.IsNullOrWhiteSpace(dr.deleteUser)
                && dr.lstMenuOrderLine != null && dr.lstMenuOrderLine.Any()).Select(dr =>
            {
                dr.lstMenuOrderLine.ForEach(ds =>
                    {
                        ds.requiredDate = string.IsNullOrWhiteSpace(ds.requiredDate) ? dr.requiredDate : ds.requiredDate;
                        ds.orderLineGuid = string.IsNullOrWhiteSpace(ds.orderLineGuid) ? Guid.NewGuid().ToString().ToUpper() : ds.orderLineGuid;
                    });
                
                return dr;
            }).SelectMany(dr => dr.lstMenuOrderLine).Where(ds => ds.purchasePolicy.ToLower() == "ondemand")
              .GroupBy(ds => new { ds.requiredDate, ds.supplierCode, ds.itemCode, ds.conversionUnit })
              .Select(ds => new MenuOrderLine()
              {
                  headGuid = requiredDateList.Where(a => a.orderDate.ToString("yyyy-MM-dd") == ds.Key.requiredDate).FirstOrDefault().headGuid,
                  itemCode = ds.Key.itemCode,
                  supplierCode = ds.Key.supplierCode,
                  requiredDate = ds.Key.requiredDate,
                  purchasePrice = ds.FirstOrDefault().purchasePrice,
                  purchaseUnit = ds.FirstOrDefault().purchaseUnit,
                  purchaseTax = ds.FirstOrDefault().purchaseTax,
                  requiredQty = ds.Sum(a => a.requiredQty) / (ds.FirstOrDefault().conversionRate == 0 ? 1: ds.FirstOrDefault().conversionRate),
                  stockUnit = ds.FirstOrDefault().stockUnit,
                  purStk_Conversion = ds.FirstOrDefault().purStk_Conversion,
                  orderLineGuid = ds.FirstOrDefault().orderLineGuid,
                  purchasePolicy = ds.FirstOrDefault().purchasePolicy,
                  conversionRate = ds.FirstOrDefault().conversionRate
            }).ToList();

            requiredDateList.ForEach(q =>
            {
                bool orderHeadDel = false;
                if (!data.Any()) orderHeadDel = true;
                // Update / Insert tblOrderHead
                string strOrderHead = (new Purchase.PurchaseOrderFactory()).SavePOHeadSQL(q.headGuid, "",q.orderDate, costCenterCode, appGuid,
                    "new", createUser, appType, orderHeadDel);
                sb.AppendFormat(strOrderHead);
               
                if (data.Any())
                {
                    List<MenuOrderLine> lines = data.Where(dr => dr.requiredDate == q.orderDate.ToString("yyyy-MM-dd")).ToList();
                    // Update 
                    //string strSqlUdateOrderLines = "UPDATE TBLORDERLINES SET DELETEUSER='{0}',DELETETIME='{1}' WHERE HEADGUID = '{2}' AND GUID='{3}' ;";
                    //sb.AppendFormat(strSqlUdateOrderLines, createUser, now, q.headGuid, q.orderLineGuid);

                    // Update/ Insert tblOrderLines
                    string strOrderLine = (new Purchase.PurchaseOrderFactory()).SavePOLineSQL(costCenterCode, appType, businessType, createUser,
                        fromType, status, now, lines, stoGuid);
                    sb.AppendFormat(strOrderLine);
                }
            });

            return sb;
        }
        #endregion

        #region 保存采购订单数据
        /// <summary>
        /// 保存采购订单
        /// </summary>
        /// <param name="param">Purchase Order</param>
        /// <returns></returns>
        public int SavePurchaseOrder(PurchaseOrderHead po, MenuOrderHead param, string appType)
        {
            int result = 0;

            StringBuilder sbSqlFinal = new StringBuilder();

            string strDateTimeNow = DateTime.Now.ToString();

            string strSqlUptRemark = " UPDATE PURCHASEORDERLINE " +
                                             " SET REMARK = N'{0}' " +
                                           " WHERE LINEGUID = '{1}' " +
                                             " AND ISNULL(REMARK, '') <> '{0}' ";

            string strSqlDelDetail = " UPDATE PURCHASEORDERLINEDETAIL " +
                                               " SET DELETEUSER = '{0}' " +
                                                 " , DELETEDATE = '{1}' " +
                                             " WHERE LINEGUID = '{2}' " +
                                               " AND DELETEUSER IS NULL ";

            string strSqlIstDetail = " INSERT INTO PURCHASEORDERLINEDETAIL " +
                                                      " ( LINEGUID " +
                                                      " , COSTUNIT " +
                                                      " , QTY " +
                                                      " , PRICE " +
                                                      " , CREATEUSER " +
                                                      " , CREATEDATE " +
                                                      " , TOTALPRICE ) " +
                                                 " VALUES " +
                                                      " ( '{0}' " +
                                                      " , '{1}' " +
                                                      " , {2} " +
                                                      " , {3} " +
                                                      " , '{4}' " +
                                                      " , '{5}' " +
                                                      " , '{6}' ) ";

                if (po.lstPurchaseOrderLine == null || po.lstPurchaseOrderLine.Count == 0)
                    return 0;

                // 按日期、公司、成本中心、供应商分组
                var lqPO = (from p in po.lstPurchaseOrderLine
                            group p by new
                            {
                                // 日期
                                orderDate = p.orderDate,
                                // 成本中心
                                costCenter = p.costCenter,
                                // 供应商
                                supplierCode = p.supplierCode,
                            });
                // PO表头Guid
                string headGuid = string.Empty;

                foreach (var gPO in lqPO)
                {
                    // 检查Head是否存在
                    headGuid = gPO.OrderByDescending(r => r.headGuid).FirstOrDefault().headGuid;
                    // 检查HeadGuid是否已经存在，没有则新建Head
                    if (string.IsNullOrWhiteSpace(headGuid))
                        sbSqlFinal.Append(CreatePurchaseOrderHead(po, strDateTimeNow, gPO.Key.orderDate, ref headGuid));

                    foreach (PurchaseOrderLine pol in gPO.ToList())
                    {
                        // 1.LineGuid不为空或删除标志为true时删除LineDetail
                        if (!string.IsNullOrWhiteSpace(pol.lineGuid) || pol.delFlag)
                        {
                            sbSqlFinal.Append(string.Format(strSqlDelDetail, po.userId, strDateTimeNow, pol.lineGuid));
                            // 删除标志为true时进入下一次循环
                            if (pol.delFlag) continue;
                        }
                        // LineGuid不为空
                        if (!string.IsNullOrWhiteSpace(pol.lineGuid))
                            // 更新Remark(Sql中判断是否更新)
                            sbSqlFinal.Append(string.Format(strSqlUptRemark, pol.remark, pol.lineGuid));
                        else
                            // 创建Line记录
                            sbSqlFinal.Append(CreatePurchaseOrderLine(po.costCenterCode, po.warehouseCode, pol, strDateTimeNow, headGuid, 0));

                        // 新增LineDetail记录
                        sbSqlFinal.Append(string.Format(strSqlIstDetail, pol.lineGuid, pol.costUnit,
                            Math.Ceiling(pol.qty), pol.price, po.userId, strDateTimeNow,
                            pol.totalPrice
                            ));
                    }
                }
                result = SqlServerHelper.Execute(SqlServerHelper.purchaseorderconn(), sbSqlFinal.ToString());
            
       
            return result;
        }

        /// <summary>
        /// 新建采购单
        /// </summary>
        /// <param name="po"></param>
        /// <returns></returns>
        private string CreatePurchaseOrderHead(PurchaseOrderHead po, string strDateTime, string strDateFormat, ref string headGuid)
        {
            headGuid = Guid.NewGuid().ToString();

            string orderDescription = po.costCenterCode.Substring(4, 3) + "-PR" + "(" + strDateFormat + ")";

            string strSql = " INSERT INTO PURCHASEORDERHEAD " +
                                      " ( HEADGUID " +
                                      " , ORDERDATE " +
                                      " , ORDERDESCRIPTION " +
                                      " , ORDERMETHOD " +
                                      " , DBCODE " +
                                      " , CREATEUSER " +
                                      " , CREATEDATE) " +
                                 " VALUES " +
                                      " ( '{0}' " +
                                      " , '{1}' " +
                                      " , '{2}' " +
                                      " , '{3}' " +
                                      " , '{4}' " +
                                      " , '{5}' " +
                                      " , '{6}' ) ";

            return string.Format(strSql, headGuid, strDateFormat, orderDescription, "001"
                , po.dbCode, po.employee, strDateTime);
        }

        /// <summary>
        /// 新建采购单行项
        /// </summary>
        /// <param name="po"></param>
        /// <returns></returns>
        private string CreatePurchaseOrderLine(string costCenter, string warehouseCode, PurchaseOrderLine pol, string strDate, string headGuid, int maxLineNumber)
        {
            string strSql = " INSERT INTO PURCHASEORDERLINE " +
                                      " ( LINEGUID " +
                                      " , HEADGUID " +
                                      " , LINENUMBER " +
                                      " , SUPPLIERCODE " +
                                      " , WAREHOUSECODE " +
                                      " , ITEMDESCRIPTION " +
                                      " , ITEMCODE " +
                                      " , UNIT " +
                                      " , REMARK " +
                                      " , COSTCENTER ) " +
                                 " VALUES " +
                                      " ( '{0}' " +
                                      " , '{1}' " +
                                      " , '{2}' " +
                                      " , '{3}' " +
                                      " , '{4}' " +
                                      " , N'{5}' " +
                                      " , '{6}' " +
                                      " , N'{7}' " +
                                      " , N'{8}' " +
                                      " , '{9}' ) ";

            pol.lineGuid = Guid.NewGuid().ToString();

            return string.Format(strSql, pol.lineGuid, headGuid, maxLineNumber, pol.supplierCode, warehouseCode
                , pol.itemDescription, pol.itemCode, pol.unit, pol.remark, costCenter);
        }

        /// <summary>
        /// 取得最大LineNumber合集
        /// </summary>
        /// <param name="lstPurchaseOrderLine"></param>
        /// <returns></returns>
        private List<PurchaseOrderLine> GetMaxLineNumberList(List<PurchaseOrderLine> lstPurchaseOrderLine)
        {
            List<PurchaseOrderLine> lstMaxLineNumber = new List<PurchaseOrderLine>();

            string strSql = " SELECT HEADGUID " +
                                 " , MAX(LINENUMBER) AS LINENUMBER" +
                              " FROM PURCHASEORDERLINE (NOLOCK) " +
                             " WHERE HEADGUID IN {0} " +
                             " GROUP BY HEADGUID ";

            // 取得HeadGuid集合字符串
            string strHeadGuid = string.Join("','", lstPurchaseOrderLine.Where(r => !string.IsNullOrWhiteSpace(r.headGuid))
                .Select(r => r.headGuid).Distinct().ToArray());

            // 无即有记录时返回
            if (string.IsNullOrWhiteSpace(strHeadGuid))
                return lstMaxLineNumber;

            strHeadGuid = "('" + strHeadGuid + "')";
            lstMaxLineNumber = SqlServerHelper.GetEntityList<PurchaseOrderLine>(SqlServerHelper.purchaseorderconn(),
                string.Format(strSql, strHeadGuid));

            return lstMaxLineNumber;
        }
        #endregion

        #region 取得即有的采购订单数据
        /// <summary>
        /// 根据HeadGuid取得PurchaseOrderLine集合
        /// </summary>
        /// <param name="headGuid"></param>
        /// <returns></returns>
        public List<PurchaseOrderLine> GetPurchaseOrderLine(PurchaseOrderHead param)
        {
            
            /*** 取得PurchaseOrderLine部分 ***/
            string strSqlLine = " SELECT H.HEADGUID " +
                                     " , L.LINEGUID " +
                                     " , CONVERT(VARCHAR(10), H.ORDERDATE, 23) AS ORDERDATE " +
                                     " , H.ORDERDESCRIPTION " +
                                     " , H.ORDERMETHOD " +
                                     " , H.DBCODE " +
                                     " , L.LINENUMBER " +
                                     " , L.SUPPLIERCODE " +
                                     " , L.WAREHOUSECODE " +
                                     " , L.ITEMDESCRIPTION " +
                                     " , L.ITEMCODE " +
                                     " , L.UNIT " +
                                     " , L.COSTCENTER " +
                                     " , L.REMARK " +
                                     " , L.ERPNUMBER " +
                                     " , D.COSTUNIT " +
                                     " , D.QTY " +
                                     " , D.PRICE " +
                                     " , D.TOTALPRICE " +
                                  " FROM PURCHASEORDERLINE AS L (NOLOCK) " +
                                 " INNER JOIN PURCHASEORDERHEAD AS H (NOLOCK) " +
                                    " ON L.HEADGUID = H.HEADGUID " +
                                 " INNER JOIN PURCHASEORDERLINEDETAIL AS D (NOLOCK) " +
                                    " ON L.LINEGUID = D.LINEGUID " +
                                   " AND D.DELETEUSER IS NULL " +
                                 //" WHERE H.ORDERDATE >= '{0}' " +
                                 " WHERE {0}" +
                                   " AND H.DBCODE = '{1}' " +
                                   " AND L.COSTCENTER = '{2}' ";

            if(param.lstRequiredDate != null && param.lstRequiredDate.Any())
            {
                string strRequiredDate = "('" + string.Join("','", param.lstRequiredDate) + "')";

                strSqlLine = string.Format(strSqlLine,
                    string.Format("(H.ORDERDATE>='{0}' OR H.ORDERDATE IN {1})", param.orderDate, strRequiredDate), param.dbCode, param.costCenterCode);
            }
            else
            {
                strSqlLine = string.Format(strSqlLine,string.Format("H.ORDERDATE>='{0}'",param.orderDate),param.dbCode,param.costCenterCode);
            }

            //List<PurchaseOrderLine> lstPOL = SqlServerHelper.GetEntityList<PurchaseOrderLine>(SqlServerHelper.purchaseorderconn(),
            //    string.Format(strSqlLine, param.orderDate, param.dbCode, param.costCenterCode));

            List<PurchaseOrderLine> lstPOL = SqlServerHelper.GetEntityList<PurchaseOrderLine>(SqlServerHelper.purchaseorderconn(),
                strSqlLine);

            return lstPOL;
        }
        #endregion

        #region 取得待分析的采购订单数据
        private List<PurchaseOrderLine> GetAllPurchaseOrderLine(MenuOrderHead param, string appType)
        {
            List<PurchaseOrderLine> lstPol = new List<PurchaseOrderLine>();
            string strSql = string.Empty;
            string strSqlLine = " SELECT L.ITEMCODE " +
                                     " , L.ITEMNAME " +
                                     " , L.ITEMDESC " +
                                     " , L.ITEMTYPE " +
                                     " , L.BOMQTY " +
                                     " , L.ACTQTY " +
                                     " , L.REMARK " +
                                     " , L.REQUIREDQTY " +
                                     " , L.SUPPLIERCODE " +
                                     " , L.SUPPLIERNAME " +
                                     " , L.CONVERSIONUNIT " +
                                     " , L.CONVERSIONRATE " +
                                     " , L.PURCHASEPRICE " +
                                     " , L.PURCHASEUNIT " +
                                     " , L.PURCHASETAX " +
                                     " , H.SOITEMGUID " +
                                     " , CONVERT(VARCHAR(100), H.REQUIREDDATE, 23) AS HEADREQUIREDDATE " +
                                     " , CONVERT(VARCHAR(100), ISNULL(L.REQUIREDDATE, H.REQUIREDDATE), 23) AS REQUIREDDATE " +
                                     " , CONVERT(VARCHAR(100), L.REQUIREDDATE, 23) AS WORKREQUIREDDATE " +
                                     " , L.ADJFLAG " +
                                  " FROM MENUORDERLINE AS L (NOLOCK) " +
                                 " INNER JOIN MENUORDERHEAD AS H (NOLOCK) " +
                                    " ON L.HEADGUID = H.HEADGUID " +
                                   " AND H.DELETEUSER IS NULL " +
                                   " AND L.DELETEUSER IS NULL  " +
                                 " WHERE H.COSTCENTERCODE = '{0}' " +
                                   " AND L.REQUIREDQTY <> 0 ";

            string strCond = " AND H.APPTYPE = '{1}' ";

            string strCond1 = "      AND ISNULL(L.REQUIREDDATE, H.REQUIREDDATE) >= '{2}' ";

            string strCond2 = "      AND ( ISNULL(L.REQUIREDDATE, H.REQUIREDDATE) >= '{2}' " +
                                      " OR ISNULL(L.REQUIREDDATE, H.REQUIREDDATE) IN {3} ) ";
            
            if (param.lstRequiredDate != null && param.lstRequiredDate.Any())
            {
                // 取得HeadGuid集合字符串
                string strRequiredDate = "('" + string.Join("','", param.lstRequiredDate.ToArray()) + "')";
                
                strSql = string.Format((strSqlLine + strCond + strCond2), param.costCenterCode, appType ,param.requiredDate, strRequiredDate);
            }
            else
            {
                strSql = string.Format((strSqlLine + strCond + strCond1), param.costCenterCode, appType, param.requiredDate);
            }

            List<MenuOrderLine> lstMenuOrderLine = SqlServerHelper.GetEntityList<MenuOrderLine>(SqlServerHelper.salesorderConn(), strSql);

            if (lstMenuOrderLine == null || !lstMenuOrderLine.Any())
                return lstPol;

            //lstMenuOrderLine.RemoveAll(r => "adj".Equals(r.adjFlag) && r.requiredQty == 0);

            #region RequiredDate、供应商相关信息调整
            MenuOrderLine mol = new MenuOrderLine();

            lstMenuOrderLine = lstMenuOrderLine.GroupBy(q => new { q.itemCode, q.requiredDate })
                .SelectMany(q =>
                {
                    MenuOrderLine firstLine = q.OrderBy(p => p.headRequiredDate).FirstOrDefault();
                    return q.Select(p =>
                    {
                        p.supplierCode = firstLine.supplierCode;
                        p.supplierName = firstLine.supplierName;
                        p.purchasePrice = firstLine.purchasePrice;
                        p.purchaseTax = firstLine.purchaseTax;
                        p.remark = q.OrderByDescending(r => r.remark).FirstOrDefault().remark;
                        return p;
                    });
                }).ToList();

            #endregion

            // 将remark有值的往前放
            //lstMenuOrderLine = lstMenuOrderLine.OrderBy(r => r.requiredDate).OrderBy(r => r.itemCode).OrderByDescending(r => r.remark).ToList();

            // 按itemCodew
            lstPol = (from line in lstMenuOrderLine
                      group line by new { line.requiredDate, line.itemCode } into lqLine
                      let firstLine = lqLine.FirstOrDefault()
                      let requiredQty = (decimal)lqLine.Sum(r => r.requiredQty)
                      select new PurchaseOrderLine
                      {
                          orderDate = firstLine.requiredDate,
                          itemCode = firstLine.itemCode,
                          itemDescription = firstLine.itemName,
                          costCenter = param.costCenterCode,
                          supplierCode = firstLine.supplierCode,
                          unit = firstLine.purchaseUnit,
                          remark = firstLine.remark,
                          price = firstLine.purchasePrice,
                          qty = requiredQty,
                          totalPrice = Math.Round( (decimal)firstLine.purchasePrice * (decimal)(1+firstLine.purchaseTax),6)
                      } into lines
                      select lines).ToList();

            return lstPol;
        }
        #endregion

        public int SubmitPO(string headGuid, DateTime date, string costCenterCode, string appGuid,
            string appStatus, string createUser, string appType)
        {
            int i = 0;
            try
            {
                string sql = SavePOHeadSQL(headGuid,"", date, costCenterCode, appGuid, 
                    appStatus, createUser, appType);
                if (string.IsNullOrWhiteSpace(sql)) throw new Exception("No SQL about PO Head");
                i = SqlServerHelper.Execute(SqlServerHelper.salesorderConn(), sql);
            }
            catch(Exception e)
            {
                throw e;
            }
            return i;
        }
        /// <summary>
        /// Update TBLORDERHEAD MENUSTATUS
        /// </summary>
        /// <param name="costCenterCode"></param>
        /// <param name="orderDate"></param>
        /// <param name="appGuid"></param>
        /// <param name="menuStatus"></param>
        /// <param name="createUser"></param>
        /// <returns></returns>
        public string SubmitPO(string costCenterCode, DateTime orderDate, string appGuid, 
            string appStatus, string createUser, string appType)
        {
            string headGuid = "";
            string sql = "select top 1 headguid from tblorderhead (nolock) where costCenterCode='{0}' " +
                        " and active=1 and orderDate='{1}' and appStatus <> 'submitted' ";
            sql = string.Format(sql, costCenterCode, orderDate, appStatus);
            // Get HeadGuid
            var dt = SqlServerHelper.GetDataScalar(SqlServerHelper.salesorderConn(), sql);
            if (dt == null) return null;
            headGuid = dt.ToStringTrim();
            // Update tblOrderHead MenuStatus => submitted
            return SavePOHeadSQL(headGuid,"", orderDate, costCenterCode, appGuid, appStatus, createUser, appType);
        }
        /// <summary>
        /// update tblOrderHead MenuStatus => submitted
        /// </summary>
        /// <param name="headGuid"></param>
        /// <param name="date"></param>
        /// <param name="costCenterCode"></param>
        /// <param name="appGuid"></param>
        /// <param name="menuStatus"></param>
        /// <param name="createUser"></param>
        /// <param name="menuType"></param>
        /// <returns></returns>
        private string SavePOHeadSQL(string headGuid, string orderNo, DateTime date, string costCenterCode,string appGuid,
            string appStatus, string createUser,  string appType=null, bool orderHeadDel=false)
        {
            return string.Join(";", SavePOHeadSQL(headGuid, orderNo, date.ToString("yyyy-MM-dd"),costCenterCode,appGuid,appStatus,createUser, appType, orderHeadDel));
        }

        private string SavePOLineSQL(string costCenterCode, string appType, string businessType, string createUser,string fromType, string status, string now,
            List<MenuOrderLine> data, string stoGuid)
         {
            StringBuilder sb = new StringBuilder();

            data = data.Distinct(dr => new { dr.headGuid, dr.itemCode, dr.supplierCode, dr.requiredDate, dr.purchasePrice, dr.purchaseUnit, dr.purchaseTax }).ToList();

            data.ForEach(q =>
            {
                string strSqlUdateOrderLines = $" UPDATE TBLORDERLINES SET DELETEUSER='{createUser}',DELETETIME='{now}' " +
                   $" WHERE HEADGUID = '{q.headGuid}' AND ISNULL(RemainQty,0) = QTY AND GUID IN " +
                   $"(SELECT ORDERLINEGUID FROM MENUORDERLINE (NOLOCK) WHERE SAVETOORDERGUID = '{stoGuid}') AND ITEMCODE='{q.itemCode}' ;";
                sb.Append(strSqlUdateOrderLines);
                string strSqlNewOrderLines = "";
                // New OrderLines
                strSqlNewOrderLines = " INSERT INTO TBLORDERLINES " +
                                " (HEADGUID, APPTYPE, CREATEUSER, STATUS, FROMTYPE, BUSINESSTYPE, COSTCENTERCODE, " +
                                " ITEMCODE, SUPPLIERCODE, ORDERDATE, ITEMPRICE, UOM, ITEMTAX, QTY, REMAINQTY, " +
                                " STOCK_UNIT,PURSTK_CONVERSION, GUID) " +
                                $" SELECT '{q.headGuid}',N'{appType}', N'{createUser}', N'{status}', N'{fromType}', N'{businessType}','{costCenterCode}',  " +
                                $" '{q.itemCode}','{q.supplierCode}','{q.requiredDate}','{q.purchasePrice}',N'{q.purchaseUnit}', '{q.purchaseTax}'," +
                                //$" '{q.requiredQty}','{q.requiredQty}'" +
                                $" sum(A.requiredQty)/{ (q.conversionRate == 0 ? 1: q.conversionRate)}, " +
                                $" sum(A.requiredQty)/{(q.conversionRate == 0 ? 1: q.conversionRate)}, " +
                                $" N'{q.stockUnit}', '{q.purStk_Conversion}', '{q.orderLineGuid}' " +
                                $" FROM MENUORDERLINE (NOLOCK) A, MENUORDERHEAD (NOLOCK) B " +
                                $" WHERE A.HEADGUID=B.HEADGUID AND B.COSTCENTERCODE = '{costCenterCode}' " +
                                $" AND A.itemCode='{q.itemCode}' AND ISNULL(A.REQUIREDDATE, B.REQUIREDDATE)= '{q.requiredDate}' " +
                                $" AND A.SAVETOORDERGUID = '{stoGuid}' " +
                                $" AND A.D ELETETIME IS NULL AND B.DELETETIME IS NULL HAVING SUM(A.REQUIREDQTY)>0;";

                strSqlNewOrderLines += $"UPDATE MENUORDERLINE SET ORDERLINEGUID = '{q.orderLineGuid}' WHERE LINEGUID IN " +
                                "(SELECT A.LINEGUID FROM MENUORDERLINE (NOLOCK) A, MenuOrderHead (NOLOCK) B " +
                                " WHERE A.HEADGUID = B.HEADGUID AND B.DELETETIME IS NULL AND A.DELETETIME IS NULL " +
                                $" AND B.CostCenterCode = '{costCenterCode}' AND ISNULL(A.RequiredDate, B.RequiredDate)= '{q.requiredDate}' " +
                                $" AND A.ItemCode = '{q.itemCode}' AND A.SAVETOORDERGUID = '{stoGuid}');";
                
                sb.Append(strSqlNewOrderLines);
                
            });
            return sb.ToString();
        }

        /*
         * 收货后，剩余数量置0
         */
        public string SavePOStockinSQL(string headGuid, string supplierCode)
        {
            return "update tblOrderLInes set RemainQty=0 where deleteuser is null and isnull(RemainQty,0)<>0 and " +
                $"HeadGUID='{headGuid}' and supplierCode='{supplierCode}';";
        }

        public List<string> SavePOHeadSQL(string headGuid, string orderNo, string date, string costCenterCode, string appGuid,
            string appStatus, string createUser , string appType=null, bool orderHeadDel=false, bool updateStock = false)
        {
            List<string> list = new List<string>();

            list.Add( $" UPDATE TBLORDERHEAD SET ACTIVE='0' WHERE HEADGUID='{headGuid}' AND ACTIVE=1 "); // ANGEL修改意见：COMPLETED<>1 ?

            string apptype1 = "";
            string apptype2 = "";
            string sql = "";
            if (appType != null)
            {
                apptype1 = ",APPTYPE";
                apptype2 = string.Format(",'{0}'", appType);
            }

            if (string.IsNullOrWhiteSpace(orderNo))
                orderNo = $"ISNULL((SELECT MAX(ORDERNO) FROM TBLORDERHEAD WHERE HEADGUID='{headGuid}' AND ORDERNO IS NOT NULL),'{GetPONumber()}')"; //避免出现MAX(null)
            else
                orderNo = string.Format("'{0}'", orderNo);
            if (orderHeadDel != true)
            {
                sql = "INSERT INTO TBLORDERHEAD " +
                     $" (HEADGUID, ORDERNO, ORDERDATE, COSTCENTERCODE, APPGUID, APPSTATUS, CREATEUSER {apptype1}) ";
            }
            sql += $"select '{headGuid}',{orderNo},'{date}','{costCenterCode}','{appGuid}','{appStatus}','{createUser}' {apptype2}" ;
            if (updateStock) //PO已completed，则不能存stockin 
                   sql += "WHERE NOT EXISTS " +   
                    $"(SELECT TOP 1 1 FROM TBLORDERHEAD WHERE HEADGUID='{headGuid}' AND APPSTATUS='completed');"; // ANGEL修改意见：加上GROUP BY HEADGUID, APPSTATUS，防止脏读时重复写入

            list.Add(sql);
            return list;
        }

        /// <summary>
        /// 保存PO 
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int SavePO(MenuOrderPurchaseParam param)
        {
            if (param.orderLines == null || !param.orderLines.Any())
                return 0;    
            StringBuilder sb = new StringBuilder();
            string headGuid = Guid.NewGuid().ToString();
            
            var tmplines = param.orderLines.Where(q => !string.IsNullOrWhiteSpace(q.headGuid));
            if (tmplines.Any())
                headGuid = tmplines.FirstOrDefault().headGuid;
            
            // 当前时间
            string now = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");

            #region OrderHead SQL 
            string strOrderHead = SavePOHeadSQL(headGuid, param.orderNo, param.orderDate, param.costCenterCode,
               param.appGuid, param.appStatus, param.createUser);

            sb.AppendFormat(strOrderHead);

            if (param.appStatus == "submit")
                return SqlServerHelper.Execute(SqlServerHelper.salesorderConn(), sb.ToString());
            #endregion

            #region OrderLines
           
            tmplines = param.orderLines.Where(q => q.ID != 0);
            string strOrderLine = "";
            if (tmplines.Any())
            {
                string ids = string.Join("','", tmplines.Select(q => q.ID));
                strOrderLine = $"UPDATE TBLORDERLINES SET DELETETIME='{now}', DELETEUSER='{param.createUser}' WHERE STATUS='CREATED' AND ID IN ('{ids}'); ";
                sb.Append(strOrderLine);
            }

            strOrderLine = "INSERT INTO TBLORDERLINES (HEADGUID, ITEMCODE, REMARK,"
                + "SUPPLIERCODE,COSTCENTERCODE,UOM,QTY,ITEMPRICE,ORDERDATE,FROMTYPE, APPTYPE, "
                + "BUSINESSTYPE,STATUS,CREATEUSER,ITEMTAX,ADJQTY,STOCK_UNIT, PURSTK_CONVERSION, GUID,REMAINQTY) VALUES ( "
                + " '{0}', '{1}', N'{2}', N'{3}', '{4}', N'{5}', N'{6}', '{7}', '{8}', '{9}', '{10}', N'{11}', '{12}', '{13}', '{14}', " +
                "'{15}',N'{16}','{17}', '{18}','{19}');";
            
            // 获取TransType
            List<OrderType> transType = GetTransType(param.programName);


            param.orderLines.Where(q => !string.IsNullOrWhiteSpace(q.itemCode)).ForEach(item =>
            {
                sb.AppendFormat(strOrderLine, headGuid, item.itemCode, item.remark,
                   item.supplierCode, param.costCenterCode, item.purchaseUnit, item.qty, item.purchasePrice,
                   param.orderDate, item.fromType, item.appType,
                   transType.FirstOrDefault().businessType, "created", param.createUser, item.itemTax, item.adjQty, 
                   item.stockUnit, item.purStk_Conversion, string.IsNullOrWhiteSpace(item.guid) ? Guid.NewGuid().ToString() : item.guid, item.adjQty+item.qty);
            });
            #endregion
            try
            {
                return SqlServerHelper.Execute(SqlServerHelper.salesorderConn(), sb.ToString());
            }
            catch
            {
                return 0;
            }
        }

        private string GetPONumber()
        {
            return DateTime.Now.ToString("POyyyyMMddHHmmssfff") + (new Random()).Next(10, 100);
        }

        public List<OrderLine> GetPO(string headGuid,string costCenterCode, string supplierCode,string orderType)
        {
            return GetPO(null, headGuid, costCenterCode, null, supplierCode, orderType);
        }

        public List<OrderLine> GetPO(string appGuid, string headGuid, string costCenterCode, string dateFilter, string supplierCode, string orderType)
        {
            string sql = "B.ID AS HEADID, A.ID, A.HEADGUID, A.GUID, A.COSTCENTERCODE, B.ORDERDATE, B.CREATETIME ,A.ITEMCODE, " +
                 " ISNULL(A.REMARK, '') AS REMARK, A.SUPPLIERCODE, A.ITEMPRICE AS PURCHASEPRICE, " +
                 " ISNULL(A.ITEMTAX, 0) AS ITEMTAX, A.UOM AS PURCHASEUNIT, A.FROMTYPE, A.APPTYPE, A.STATUS, " +
                 " B.APPSTATUS , B.ORDERNO, ISNULL(A.QTY, 0) AS QTY,  ISNULL(A.ADJQTY, 0) AS ADJQTY, A.REMAINQTY, A.STOCK_UNIT AS STOCKUNIT,A.PURSTK_CONVERSION ";

            sql = GetPOSQL(sql, $" and isnull(B.orderType,'') = '{orderType}' ", 
                appGuid, headGuid, costCenterCode, dateFilter, supplierCode);

            /*steve 2020-6-23
            sql = string.Format(sql, "A.ID, A.HEADGUID, A.GUID, A.COSTCENTERCODE, A.ORDERDATE, A.ITEMCODE, " +
                 " ISNULL(A.REMARK, '') AS REMARK, A.TRANSTYPEGUID, A.TRANSTYPE, A.SUPPLIERCODE, A.ITEMPRICE, " +
                 " ISNULL(A.ITEMTAX, 0) AS ITEMTAX, A.UOM, A.FROMTYPE, A.APPTYPE, A.STATUS, " +
                 " B.APPSTATUS , B.ORDERNO, SUM(ISNULL(A.QTY, 0)) AS REQUIREDQTY,  SUM(ISNULL(A.ADJQTY, 0)) AS ADJQTY " + 
                 (string.IsNullOrWhiteSpace(appGuid)? "" : ", C.APPGROUP,D3.HEADGUID HEADGUIDOFTHISAPPSUBMIT "),

                 " GROUP BY A.HEADGUID, A.GUID, A.ID, A.COSTCENTERCODE, A.ORDERDATE, A.ITEMCODE, A.REMARK, A.TRANSTYPEGUID, " +
                 " A.TRANSTYPE, A.SUPPLIERCODE, A.ITEMPRICE, A.ITEMTAX, A.UOM, A.FROMTYPE, A.APPTYPE, A.STATUS, B.ORDERNO, B.APPSTATUS" + 
                 (string.IsNullOrWhiteSpace(appGuid) ? "" : ",C.APPGROUP,D3.HEADGUID ") +
                 " ORDER BY A.ITEMCODE",
                
                 string.IsNullOrWhiteSpace(appGuid)? "" :                     
                     $"LEFT JOIN TBLORDERHEAD (NOLOCK) D3 ON D3.APPGUID = '{appGuid}' AND B.HEADGUID=D3.HEADGUID " +
                     "AND D3.APPSTATUS='SUBMITTED' ");
            */

            List<OrderLine> moh = SqlServerHelper.GetEntityList<OrderLine>(SqlServerHelper.salesorderConn(), sql);

            List<TransData> ItemStock = (new StockTrans(costCenterCode)).GetStock(dateFilter);

            if (moh == null || moh.Count == 0)
                return null;

            if (ItemStock == null || !ItemStock.Any())
                return moh;

            moh = moh.GroupJoin(ItemStock, mos => mos.itemCode, nos => nos.itemCode, (mos, nos) =>
            {
                mos.stockQty = 0;
                if(nos.FirstOrDefault() != null)
                    mos.stockQty = nos.FirstOrDefault().stockQty;
                return mos;
            }).ToList();

            return moh;
        }

        public string GetOpenPOSQL(string costCenterCode, string dateFilter,string whereFilter = null)
        {
            return GetPOSQL("{0}",whereFilter, null,null,costCenterCode, dateFilter, null);
        }

        private string GetPOSQL(string fields, string whereFilter, string appGuid, string headGuid, string costCenterCode,
            string dateFilter, string supplierCode)
        {
            whereFilter = whereFilter.ToStringTrim();
            if (fields.ToLower().Split(',').Any(q => q.ToStringTrim().ToLower().Contains("itemcode")))
                whereFilter += " ORDER BY A.ITEMCODE";

            string sql = GetPOSQL(appGuid, headGuid, costCenterCode, dateFilter, supplierCode, true);
            sql = string.Format(sql, fields +
                 (string.IsNullOrWhiteSpace(appGuid) ? "" : ", C.APPGROUP,D3.HEADGUID HEADGUIDOFTHISAPPSUBMIT "),
                 whereFilter,
                 
                 string.IsNullOrWhiteSpace(appGuid) ? "" :
                     $"LEFT JOIN TBLORDERHEAD (NOLOCK) D3 ON D3.APPGUID = '{appGuid}' AND B.HEADGUID=D3.HEADGUID " +
                     "AND D3.APPSTATUS='SUBMITTED' ");

            return sql;
        }
        
        /*
         * dateFilter: < > = 日期
         * 
         */
        private string GetPOSQL(string appGuid, string headGuid, string costCenterCode, string dateFilter, string supplierCode, 
            bool linkLines=true)
        {
            dateFilter = dateFilter.ToStringTrim();
            if (!string.IsNullOrWhiteSpace(dateFilter))
            {
                if (!">,<,=".Split(',').Any(q => dateFilter.StartsWith(q)))
                    dateFilter = string.Format("='{0}'", dateFilter);
                dateFilter = "AND B.ORDERDATE" + dateFilter;
            }
            
            supplierCode = supplierCode.ToStringTrim();
            if (!string.IsNullOrWhiteSpace(supplierCode))
                supplierCode = string.Format("AND A.SUPPLIERCODE ='{0}'", supplierCode);
            headGuid = headGuid.ToStringTrim();
            if (!string.IsNullOrWhiteSpace(headGuid))
                headGuid = string.Format("AND B.HEADGUID='{0}' ", headGuid);

            string sql = " SELECT {0} FROM TBLORDERHEAD (NOLOCK) B ";
            if (linkLines) sql += "JOIN TBLORDERLINES (NOLOCK) A ON B.HEADGUID=A.HEADGUID ";

            if (!string.IsNullOrWhiteSpace(appGuid))
                sql += " JOIN TBLAPPS(NOLOCK) C ON B.APPGUID = C.GUID  " +                //同一源数据"
                    $"JOIN TBLAPPS (NOLOCK) D2 ON D2.GUID='{appGuid}' AND " +
                    "LOWER(ISNULL(C.FirstAppGroup,''))=LOWER(ISNULL(D2.FirstAppGroup,'')) " +
                 "AND ((LOWER(ISNULL(C.APPGROUP,'')) NOT IN ('pr','prpo') OR LOWER(ISNULL(D2.APPGROUP,'')) <> 'po') " +    //PO与PR/PRPO不能共享数据
                 "AND (LOWER(ISNULL(C.APPGROUP,'')) <> 'po' OR LOWER(ISNULL(D2.APPGROUP,'')) not in ('pr','prpo'))) ";

            sql += "{2} " +    //本程序已submitted
                 "WHERE B.ACTIVE = 1 AND A.DELETEUSER IS NULL AND " +
                 "(isnull(A.QTY,0)+isnull(A.ADJQTY,0))=isnull(A.REMAINQTY,0) AND " + //未收货
                 $"B.COSTCENTERCODE='{costCenterCode}' {dateFilter} {supplierCode} {headGuid}" +
                 " {1}";
            
            return sql;
            
        }


        public Dictionary<string, object> GetPO(POItemParam param)
        {
            Dictionary<string, object> period = new Aden.DAL.AppProcess.AppProcessFactory().GetPeriod(
                  param.appGuid, param.costCenterCode, DateTime.Parse(param.date));

            Dictionary<string, object> dic = new Dictionary<string, object>();

            // Angel修改：抛到前端的OrderNO
            string orderNo = GetPONumber();
            string headGuid = Guid.NewGuid().ToString();
            string fromType = "new";
            int headID = 0;
            OrderLine line = null;
            if (!string.IsNullOrWhiteSpace(param.headGuid))
            {

                List<OrderLine> po = GetPO(param.appGuid, param.headGuid, param.costCenterCode, param.date, param.supplierCode, param.productType);
                   
                if (po != null && po.Any())
                {
                    po = po.OrderBy(dr => dr.ID).ToList();
                    /*
                        // 比较OrderDate 和 CreateTime, 取未收货的补单 (正常单： OrderDate > CreateTime && CreateTime < EndTime)
                        DateTime now = DateTime.Now;
                        DateTime orderDate = po.FirstOrDefault().orderDate;
                        DateTime createTime = po == null ? now : po.FirstOrDefault().createTime;

                        if (po != null && !string.IsNullOrWhiteSpace(param.appType) && param.appType.ToLower() == "additionmenu")
                            po = po.Where(dr => (dr.createTime.AddDays(1) >
                                        DateTime.Parse(dr.orderDate.ToString("yyyy-MM-dd") + " " + DateTime.Parse(period["EndTime"].ToString()).ToString("HH:mm:ss")))
                                        && dr.remainQty != 0).ToList();
                        if (po != null && !string.IsNullOrWhiteSpace(param.appType) && param.appType.ToLower() == "normalmenu")
                            po = po.Where(dr => !(dr.createTime.AddDays(1) >
                                        DateTime.Parse(dr.orderDate.ToString("yyyy-MM-dd") + " " + DateTime.Parse(period["EndTime"].ToString()).ToString("HH:mm:ss")))
                                    && dr.remainQty != 0).ToList();

                    */
                    dic.Add("PO", po);

                    orderNo = po.FirstOrDefault().orderNo;
                    fromType = po.FirstOrDefault().fromType;
                    headID = po.FirstOrDefault().headID;
                    headGuid = po.FirstOrDefault().headGuid;

                    line = po.FirstOrDefault();
                }
            }


            dic.Add("ID", headID);
            dic.Add("OrderNo",orderNo);
            dic.Add("HeadGuid", headGuid);
            dic.Add("FromType", fromType);

            /*
            OrderLine line = null;

            if (po != null && po.Any())
                line = po.FirstOrDefault();
*/
            //DateTime?
            Dictionary<string, object> time = CheckPODatas(period, line == null ? "" : line.appStatus, 
                line == null ? "" : line.appGroup ,line == null ? "" : line.headGuidOfThisAppSubmit);

            //dic.Add("DeadLine", time ?? null);
            dic.Add("DeadLine", ((DateTime)period["EndTime"]).Subtract(DateTime.Now.AddMinutes(1)).TotalSeconds.ToInt());// time.ContainsKey("DeadLine") ? time["DeadLine"] : null);
            dic.Add("Remark",  time.ContainsKey("remark") ? time["remark"] : null);            

            return dic;
        }
        //DateTime?
        private Dictionary<string, object> CheckPODatas(Dictionary<string,object> period, string appStatus, string appGrp,
            string headGuidOfThisAppSubmit)
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();
            //没有流程
            if (period == null) 
            {
                dic.Add("remark", null);
                return dic;
                //return null;
            }

            DateTime now = DateTime.Now;
            // 过期
            if (now >= (DateTime)period["EndTime"])
            {
                dic.Add("remark", "Expired");
                return dic;
                // return null;
            }
                    
            if (!string.IsNullOrWhiteSpace(headGuidOfThisAppSubmit))
            {
                dic.Add("remark", "Submitted!");
                return dic;
                //return null;
            }

            dic.Add("DeadLine", ((DateTime)period["EndTime"]).Subtract(DateTime.Now.AddMinutes(1)).TotalSeconds.ToInt());

            if (string.IsNullOrWhiteSpace(appGrp))
                return dic;

            //本程序在修改
            if (appGrp == period["ThisApp"].ToStringTrim())
                return dic;

            #region order在本app之前
            appStatus = appStatus.ToLower();
            if (appStatus == "submitted" && appGrp == period["LastApp"].ToStringTrim())
                return dic;

            if (!period.ContainsKey("StartTime") || now < (DateTime)period["StartTime"])
            {
                dic.Remove("DeadLine");
                dic.Add("remark", string.Format("Waiting to submit {0} ", period["LastApp"].ToStringTrim()));
                return dic;
                //return null;
            }

            return dic; 
            #endregion
        }
        /// <summary>
        /// PO日期选项
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<Dictionary<string, object>> GetPODates(string appGuid, string costCenterCode,string productType)
        {
            Dictionary<string, object> dic = new AppProcessFactory().GetPeriod(appGuid, costCenterCode);
            if (dic["FirstApp"].ToStringTrim().ToLower() == "menuorder")
                return PODates(appGuid, costCenterCode);

            //Food的Addition和NoFood
            DateTime endTime = (DateTime)dic["EndTime"];
            /*
            string sqlAO = "";
            if (productType.ToLower() == "food")
            {
                sqlAO = "and dateadd(d, {0}, a1.CreateTime) >= cast(a1.orderdate as varchar(10)) + ' {1}'";
                sqlAO = string.Format(sqlAO, dic["DaysToChangeOfThis"], endTime.TimeOfDay);
            }*/
            string sqlAO = "select distinct a1.orderdate,a1.headguid,a1.createtime,isnull(a1.orderNo,'') orderNo " +
                // " case when dateadd(d,1,a1.CreateTime)< cast(a1.orderdate as varchar(10)) + ' {1}' then 'normal' else 'addition' end poType, " +
                //" ,case when a4.appGroup in ('PR','PO') then 1 else 0 end EnableAddition  " +
                " from tblorderhead (nolock) a1 " +// join tblOrderLines (nolock) a2 on a1.headguid = a2.headguid" +
                " join tblApps (nolock) a4 on a4.guid=a1.appguid " +
                " left join(select distinct HeadGUID from tblOrderLines " +                     //收货
                " where DeleteTime is null and isnull(RemainQty,0)<(isnull(Qty,0)+isnull(AdjQty,0))) a3 on a1.HeadGUID = a3.HeadGUID " +
                " where a1.costcentercode = '{0}' and a1.active = 1 " + //and a2.DeleteTime is null and (isnull(a2.Qty,0)+isnull(a2.AdjQty,0)) = isnull(a2.RemainQty,0) " +
                "and a3.HeadGUID is null and a1.orderType='{1}' and a4.appGroup in ('PR','PO') " +
                " order by a1.orderdate; ";//case when dateadd(d,1,a1.CreateTime)< cast(a1.orderdate as varchar(10)) + ' {1}' then 'normal' else 'addition' end, ";
                        
            //DateTime endtime = new AppProcess.AppProcessFactory().GetEndTime(appGuid, costCenterCode, null);

            sqlAO = string.Format(sqlAO, costCenterCode, productType);
            DataTable dt = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sqlAO);
            var dates = dt.AsEnumerable();

            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();

            bool NonFoodType = productType.ToLower() != "food";
            DateTime now = DateTime.Now;
            DateTime date = now.AddDays(dic["DaysToChangeOfThis"].ToInt());
            string orderNo = "";
            if(productType.ToLower() == "food")
            {            
                //普通
                var normalFood = dates.Where(q => DateTime.Parse(q.Field<DateTime>("orderdate").ToString("yyyy-MM-dd " + endTime.ToString("HH:mm")))
                    > q.Field<DateTime>("createtime").AddDays(dic["DaysToChangeOfThis"].ToInt()));
                //这天可以显示下周
                int WeekDayOfWeekPlanning = 4;
                //显示周计划的周数
                int NbrOfWeekPlanning = 1;

                string sendTime = endTime.ToString("HH:mm");
                if (now.TimeOfDay > endTime.TimeOfDay)
                {
                    date = date.AddDays(1);
                    sendTime = "";
                }

                int WeekDayOfnow = (int)now.DayOfWeek;
                int WeekDayOfdate = (int)date.DayOfWeek;
                if (WeekDayOfnow == 0) WeekDayOfnow = 7;
                if (WeekDayOfdate == 0) WeekDayOfdate = 7;
                
                string remark = "Next Week";
                if ((date - now).Days < 7 && WeekDayOfdate >= WeekDayOfnow)
                    remark = "This Week";

                while(true)
                {
                    string guid = "";
                    if (normalFood.Any())
                    {
                        var tmpfood = normalFood.Where(q => q.Field<DateTime>("orderdate").ToString("yyyy-MM-dd") == date.ToString("yyyy-MM-dd"));
                        if (tmpfood.Any())
                        {
                            guid = tmpfood.FirstOrDefault().Field<string>("headguid");
                            orderNo = tmpfood.FirstOrDefault().Field<string>("orderNo");
                        }
                    }

                    list.Add(new Dictionary<string, object> { {"date", date.ToString("yyyy-MM-dd") }, { "headGuid", guid }, {"orderNo", orderNo},
                        { "remark", remark } , {  "EndTime", sendTime} });
                    sendTime = "";

                    if (date.DayOfWeek == DayOfWeek.Sunday)
                    {
                        //周四开始才能做周计划
                        if (WeekDayOfnow < WeekDayOfWeekPlanning)
                            break;

                        if (remark == "Next Week" && --NbrOfWeekPlanning == 0)
                            break;

                        remark = "Next Week";
                    }
                    date = date.AddDays(1);
                }

                //补单
                dates = dates.Where(q => DateTime.Parse(q.Field<DateTime>("orderdate").ToString("yyyy-MM-dd " + endTime.ToString("HH:mm")))
                    <= q.Field<DateTime>("createtime").AddDays(dic["DaysToChangeOfThis"].ToInt()));
            }

            /*
            do
            {
                double dbl = (date - now).TotalDays;
                int intDow = Convert.ToInt32(date.DayOfWeek);
                if (intDow == 0) intDow = 7;
                var dt1 = dates.AsEnumerable().Where(dr => dr.Field<string>("poType") == "normal" 
                    && dr.Field<DateTime>("orderDate").ToString("yyyy-MM-dd") == date.ToString("yyyy-MM-dd"));

                string headGuid = !dt1.Any()? "": dt1.FirstOrDefault().Field<string>("headGuid");

                list.Add(new Dictionary<string, object> { {"date", date.ToString("yyyy-MM-dd") }, { "headGuid", headGuid },
                    { "remark", (dbl >= 7 || dbl >= intDow)? "Next Week" : "This Week" }, { "EndTime",  endTime} });
                date = date.AddDays(1);
            } while (((date - now).Days < 24 || date.DayOfWeek != DayOfWeek.Monday) && !NonFoodType);
            */
            /* 补单日期 
               条件：EnableAddition = 1
            */

            if (dates != null || dates.Any())    
                list.AddRange(dates.Select(item =>
                {
                    Dictionary<string, object> obj = new Dictionary<string, object>();
                    obj.Add("date", item.Field<DateTime>("orderdate").ToString("yyyy-MM-dd"));
                    obj.Add("headGuid", item.Field<string>("headGuid").ToString());
                    obj.Add("orderNo", item.Field<string>("orderNo").ToString());
                    obj.Add("remark", !NonFoodType ? "Additional Order" : "NoFood Order");
                    obj.Add("EndTime","");
                    return obj;
                }));
                
            list.Add(new Dictionary<string, object> { { "date", "" },{ "remark", "New Additional Order" }, { "EndTime", "" } });
            return list;
        }

        public List<Dictionary<string, object>> PODates(string appGuid,string costCenterCode)
        {
            string date = string.Format(">'{0}'", DateTime.Now.ToString("yyyy-MM-dd"));
            string sql = string.Format(GetPOSQL(appGuid,null,costCenterCode,date,null,false),
                "DISTINCT CONVERT(VARCHAR(10), B.ORDERDATE, 23) ORDERDATE,HEADGUID,ORDERNO ","", "","");
           
           return SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), 
                    sql).AsEnumerable().GroupBy(dr=> new {date=dr.Field<string>("ORDERDATE") }).
                    Select(dr => {
                        Dictionary<string, object> obj = new Dictionary<string, object>();
                        obj.Add("date",dr.Key.date);
                        if (dr.Count()> 1)
                        {
                            obj.Add("lines", dr.Select(q => new
                            {
                                orderNo = q.Field<string>("ORDERNO"),
                                headGuid = q.Field<string>("headGuid"),
                            }));
 
                            return obj;
                        }

                        obj["date"] = dr.Key.date + " " + dr.FirstOrDefault().Field<string>("ORDERNO");
                        obj.Add("headGuid", dr.FirstOrDefault().Field<string>("headGuid"));
                        
                        return obj;
                    }).ToList();

        }
        public List<Dictionary<string, object>> ClosePODates(string costCenterCode)
        {
            List<Dictionary<string, object>> dic = new List<Dictionary<string, object>>();
            string date = string.Format("<='{0}'", DateTime.Now.ToString("yyyy-MM-dd"));
            // 入库数量>0
            string sql = " SELECT DISTINCT CONVERT(VARCHAR(10), B.ORDERDATE, 23) ORDERDATE, B.ORDERNO, B.HEADGUID " +
                "FROM TBLORDERHEAD(NOLOCK) B JOIN TBLORDERLINES(NOLOCK) A ON B.HEADGUID = A.HEADGUID AND A.DELETEUSER IS NULL " +
                "JOIN TBLTRANSDATA(NOLOCK) C ON C.OrderLineGUID = A.Guid " +
                "WHERE B.ACTIVE = 1 AND B.COSTCENTERCODE = '{0}' AND B.ORDERDATE {1} " +
                "AND ISNULL(A.RemainQty,0)= 0 GROUP BY B.ORDERDATE,B.ORDERNO, B.HEADGUID HAVING SUM(C.Qty) > 0"; 
            sql = string.Format(sql, costCenterCode, date);

            var data = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sql).AsEnumerable().GroupBy(q => q.Field<string>("orderDate")).
                Select(dr => {
                Dictionary<string, object> obj = new Dictionary<string, object>();
                
                obj["date"] = dr.Key;
                obj["orderNo"] = dr.FirstOrDefault().Field<string>("orderNo");
                //obj["lines"] = dr.Select(q => new
                //{
                //    index = new
                //    {
                //        headGuid = q.Field<string>("headGuid"),
                //        orderNo = q.Field<string>("orderNo")
                //    },
                //    label = q.Field<string>("orderNo")
                //});
                dic.Add(obj);
                return dic;
            }).ToList();
            return dic;
        }
        public List<Dictionary<string, object>> OpenPODates(string costCenterCode, DateTime endTime)
        {
            List<Dictionary<string, object>> dic = new List<Dictionary<string, object>>();

            string date = string.Format("<='{0}'", DateTime.Now.ToString("yyyy-MM-dd"));
        
            string sql = string.Format(GetOpenPOSQL(costCenterCode, null, 
                " and B.AppStatus='submitted' and isnull(A.qty,0)+isnull(A.adjQty,0)>0 {1} " 
                //" and isnull(A.RemainQty,0)<>0 and A.RemainQty=isnull(A.Qty,0)+isnull(A.adjQty,0) " +
                //$"and (dateadd(d,1,B.CreateTime)>=cast(B.orderdate as varchar(10))+' {endTime.TimeOfDay}' or B.orderdate>=( " +
                //$"select top 1 max(a1.OrderDate) from tblOrderHead a1, tblOrderLines a2 where a1.CostCenterCode='{costCenterCode}' " +
                //"and a1.HeadGUID=a2.HeadGUID and a2.DeleteTime is null and a2.RemainQty<a2.Qty)) "{1}"
                ),
                " DISTINCT CONVERT(VARCHAR(10), B.ORDERDATE, 23) ORDERDATE,A.SUPPLIERCODE, B.ORDERNO, B.HEADGUID, B.ORDERTYPE, isnull(B.ORDERSUBTYPE,'NoFood') AS POTYPE " 
                //$"CASE WHEN B.ORDERTYPE='NOFOOD' THEN 'NOFOOD' WHEN DATEADD(D,1,B.CREATETIME)>=CAST(B.ORDERDATE AS VARCHAR(10))+' {endTime.TimeOfDay}' " +
                //" THEN 'ADDITION' ELSE 'NORMAL' END POTYPE"
                , "");

            string sqlSuppliers = "SELECT SUPPLIER_CODE AS SUPPLIERCODE, SUPPLIER_NAME AS SUPPLIERNAME " +
                " FROM [dbo].[DM_D_ERP_SUPPLIER] (NOLOCK) ;";

            // 供应商明细
            var SupplierList = SqlServerHelper.GetDataTable(SqlServerHelper.purchasepriceconn(), sqlSuppliers);

            var orderDateList = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sql);

            var tmp = orderDateList.AsEnumerable().GroupJoin(SupplierList.AsEnumerable(),
                mos => mos.Field<string>("supplierCode"), nos => nos.Field<string>("supplierCode"),
                (mos, nos) => new { mos, nos }).Where(q => !string.IsNullOrWhiteSpace(q.mos.Field<string>("supplierCode"))).Select(q => q).ToList();
        
            var data = orderDateList.AsEnumerable().GroupJoin(SupplierList.AsEnumerable(),
                mos => mos.Field<string>("supplierCode"), nos => nos.Field<string>("supplierCode"),
                (mos, nos) => new { mos, nos }).Where(q => !string.IsNullOrWhiteSpace(q.mos.Field<string>("supplierCode"))).
                GroupBy(q => new { poType = q.mos.Field<string>("poType"), orderDate = q.mos.Field<string>("orderDate")}).
                Select(dr => {
                    Dictionary<string, object> obj = new Dictionary<string, object>();
                    string supplierName = string.Empty;
                  
                    obj["date"] = dr.Key.orderDate;
                    obj["poType"] = dr.Key.poType;

                    obj["lines"] = dr.Select(q => new
                    {
                        index = new
                        {
                            headGuid = q.mos.Field<string>("headGuid"),
                            supplierCode = q.mos.Field<string>("supplierCode"),
                            supplierName = (q.nos == null || !q.nos.Any()) ? "" : q.nos.FirstOrDefault().Field<string>("supplierName"),
                            orderNo = q.mos.Field<string>("orderNo"),
                            orderType = q.mos.Field<string>("orderType")
                        },

                        label = q.mos.Field<string>("supplierCode") + " "
                                    + ((q.nos == null || !q.nos.Any()) ? "" : q.nos.FirstOrDefault().Field<string>("supplierName")) //q.nos.Where(t => t.Field<string>("supplierCode") == q.mos.Field<string>("supplierCode")).FirstOrDefault().Field<string>("supplierName")
                                    + " (" + q.mos.Field<string>("orderNo") + ")"
                    }); ;
                    dic.Add(obj);
                    return dic;
                }).ToList();
            return dic;
        }
    }
}